<?php
require_once ($_SERVER['DOCUMENT_ROOT'].'/global.php');
include_once ($_SERVER['DOCUMENT_ROOT'].'/PHPExcel/Classes/PHPExcel.php');
$objPHPExcel = new PHPExcel();
/*以下是一些设置 ，什么作者  标题啊之类的*/
$objPHPExcel->getProperties()->setCreator("www.evuan.com")
->setLastModifiedBy('DingJinBiao')
->setTitle("DingJinBiao")
->setSubject("DingJinBiao")
->setDescription("DingJinBiao")
->setKeywords("DingJinBiao")
->setCategory("DingJinBiao");
/////////////////////////////////////////////
$biaoti = $_GET['BeginTime'].'至'.$_GET['EndTime'].'统计数据';
$array = array();
$BeginTime = $_GET['BeginTime'].' 00:00:00';
$EndTime = $_GET['EndTime'].' 23:59:59';
$sql="SELECT count(1) allnum,count(Status=1 or null) agree,count(Status=2 or null) disagree ,count(Status=0 or null) waitnum,(sum(if(Status=1,Minutes,0)) div 60) hours,(sum(if(Status=1,Minutes,0)) mod 60) Minutes,sum(if(Status=1,Minutes,0))*0.2 allPay  from GongZuoRecord where IsDelete = 0 and str_to_date(BeginTime, '%Y-%m-%d %T') >= str_to_date('{$BeginTime}', '%Y-%m-%d %T') and str_to_date(BeginTime, '%Y-%m-%d %T') <= str_to_date('{$EndTime}', '%Y-%m-%d %T')  ".($_GET['BuMenId'] == '1'?'':"and BuMenId = {$_GET['BuMenId']}");
$query = $mysqli->query($sql);
$array = $query->fetch_assoc();
$array['list'] = array();
$sql = "SELECT b.id,b.XueHao,b.XingMing,b.Phone,IdNumber,BankName,BankIdNumber,count(1) CiShu,sum(Minutes) div 60 hours,sum(Minutes) mod 60 Minutes ,sum(Minutes)*0.2 Pay from GongZuoRecord a,user b where a.IsDelete = 0 and a.person_id = b.id and a.Status = 1  and str_to_date(a.BeginTime, '%Y-%m-%d %T') >= str_to_date('{$BeginTime}', '%Y-%m-%d %T') and str_to_date(a.BeginTime, '%Y-%m-%d %T') <= str_to_date('{$EndTime}', '%Y-%m-%d %T')  ".($_GET['BuMenId'] == '1'?'':"and BuMenId = {$_GET['BuMenId']}")." group by b.id,b.XingMing order by b.XingMing";
$query = $mysqli->query($sql);
while ($a = $query->fetch_assoc()) {
  $array['list'][] = $a;
}
$array['mingxi'] = array();
$sql = "
SELECT
    a.id,
    b.BuMenName,
    c.XingMing sXingMing,
    c.XueHao,
    a.BeginTime,
    a.EndTime,
    a.ReferTime,
    a.Minutes,
    a.Minutes*0.2 Pay,
    a.NeiRong,
    d.XingMing tXingMing,
    a.ShenHeTime
from
    GongZuoRecord a,
    BuMenTree b,
    user c,
    user d
where
    a.IsDelete = 0
    and a.BuMenId = b.id
    and a.person_id = c.id
    and a.ShenHeRen = d.id
    and a.Status = 1
    and str_to_date(a.BeginTime, '%Y-%m-%d %T') >= str_to_date('{$BeginTime}', '%Y-%m-%d %T')
    and str_to_date(a.BeginTime, '%Y-%m-%d %T') <= str_to_date('{$EndTime}', '%Y-%m-%d %T')  "
    .($_GET['BuMenId'] == '1'?'':"and a.BuMenId = {$_GET['BuMenId']}")."
order by
    a.BeginTime
";
$query = $mysqli->query($sql);
while ($a = $query->fetch_assoc()) {
  $array['mingxi'][] = $a;
}
$objPHPExcel->setActiveSheetIndex(0)->setTitle('汇总表')
->setCellValue('A1', $biaoti)->getStyle('A1')->getFont()->setName('宋体')->setSize(18);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:I1');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A2', '序号')
->setCellValue('B2', '姓名')
->setCellValue('C2', '学号')
->setCellValue('D2', '手机号')
->setCellValue('E2', '身份证号')
->setCellValue('F2', '开户行')
->setCellValue('G2', '银行卡号')
->setCellValue('H2', '提交次数')
->setCellValue('I2', '工作时长')
->setCellValue('J2', '结算金额(元)');

for ($i=0; $i < count($array['list']); $i++) {
    @$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A'.($i+3), ($i+1))
    ->setCellValue('B'.($i+3), $array['list'][$i]['XingMing'])
    ->setCellValue('C'.($i+3), $array['list'][$i]['XueHao'])
    ->setCellValue('D'.($i+3), $array['list'][$i]['Phone'].' ')
    ->setCellValue('E'.($i+3), $array['list'][$i]['IdNumber'].' ')
    ->setCellValue('F'.($i+3), $array['list'][$i]['BankName'])
    ->setCellValue('G'.($i+3), $array['list'][$i]['BankIdNumber'].' ')
    ->setCellValue('H'.($i+3), $array['list'][$i]['CiShu'])
    ->setCellValue('I'.($i+3), $array['list'][$i]['hours'].'小时'.$array['list'][$i]['Minutes'].'分钟')
    ->setCellValue('J'.($i+3), $array['list'][$i]['Pay']);
}
//明细表
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1)->setTitle('明细表')
->setCellValue('A1', $biaoti)->getStyle('A1')->getFont()->setName('宋体')->setSize(18);
$objPHPExcel->setActiveSheetIndex(1)->mergeCells('A1:L1');
$objPHPExcel->setActiveSheetIndex(1)
->setCellValue('A2', '序号')
->setCellValue('B2', '部门')
->setCellValue('C2', '学生姓名')
->setCellValue('D2', '学号')
->setCellValue('E2', '开始时间')
->setCellValue('F2', '结束时间')
->setCellValue('G2', '提交时间')
->setCellValue('H2', '工作时长')
->setCellValue('I2', '工作内容')
->setCellValue('J2', '审核教师')
->setCellValue('K2', '审核时间')
->setCellValue('L2', '结算金额(元)')
->setCellValue('M2', '数据库ID');

for ($i=0; $i < count($array['mingxi']); $i++) {
    @$objPHPExcel->setActiveSheetIndex(1)
    ->setCellValue('A'.($i+3), ($i+1))
    ->setCellValue('B'.($i+3), $array['mingxi'][$i]['BuMenName'])
    ->setCellValue('C'.($i+3), $array['mingxi'][$i]['sXingMing'])
    ->setCellValue('D'.($i+3), $array['mingxi'][$i]['XueHao'])
    ->setCellValue('E'.($i+3), $array['mingxi'][$i]['BeginTime'])
    ->setCellValue('F'.($i+3), $array['mingxi'][$i]['EndTime'])
    ->setCellValue('G'.($i+3), $array['mingxi'][$i]['ReferTime'])
    ->setCellValue('H'.($i+3), $array['mingxi'][$i]['Minutes'])
    ->setCellValue('I'.($i+3), $array['mingxi'][$i]['NeiRong'])
    ->setCellValue('J'.($i+3), $array['mingxi'][$i]['tXingMing'])
    ->setCellValue('K'.($i+3), $array['mingxi'][$i]['ShenHeTime'])
    ->setCellValue('L'.($i+3), $array['mingxi'][$i]['Pay'])
    ->setCellValue('M'.($i+3), $array['mingxi'][$i]['id']);
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$biaoti.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>
